﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;


namespace NuaTarDoneng
{
    public  class tailSql
    {
        public static string Login(string usr, string pwd)
        {
            string result = "";
            SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "select count(*) from Tail where usr='"+usr+"' and pwd='"+pwd+"'";
            cnn.Open();
            int i = (int)cmd.ExecuteScalar();
            cnn.Close();
            if (1 == i)
            {
                result = "OK";
            }
            else 
            {

                result = "ER";
            }
            return result;
        }
        public static string getDid(string uid)
        {
            string result = "";
            SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "select deviceID from Tail where uid='" + uid + "'";
            cnn.Open();
            result = Convert.ToString(cmd.ExecuteScalar());
            cnn.Close();
            
            return result;
        
        }
        public static string getUid(string did)
        {
            string result = "";
            SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "select usrID from Tail where deviceID='" + did + "'";
            cnn.Open();
            result = Convert.ToString(cmd.ExecuteScalar());
            cnn.Close();
            return result;

        }
        public static string getpid(string uid)
        {
            string result = "";
            SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "select pwd from Tail where uid='" + uid + "'";
            cnn.Open();
            result = Convert.ToString(cmd.ExecuteScalar());
            cnn.Close();
            return result;

        }
        public static string getDidAPid(string uid)
        {
            return getDid(uid) + "#" + getpid(uid);
        }
        public static string LoginDevice(string usr, string deviceid)
        {
            string result = "";
            SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "select count(*) from Tail where usr='" + usr + "' and deviceID='" + deviceid + "'";
            cnn.Open();
            int i = (int)cmd.ExecuteScalar();
            cnn.Close();
            if (1 == i)
            {
                result = "OK";
            }
            else
            {

                result = "ER";
            }
            return result;
        }
        public static string RegIt(string usr, string pwd,string did,string uid,string code)
        {
            string result = "";
            SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "insert into Tail (usrID,deviceID,usr,pwd,code) values('"+uid+"','"+did+"','"+usr+"','"+pwd+"','"+code+"') ";
            cnn.Open();
            int i = (int)cmd.ExecuteNonQuery();
            cnn.Close();
            if (1 == i)
            {
                result = "OK";
            }
            else
            {

                result = "ER";
            }
            return result;
        }
       public static string UpdateInfo(string usrID,string name,string info)
       {
           string result = "";
           SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
           SqlCommand cmd = cnn.CreateCommand();
           cmd.CommandText = "update Tail set "+name+"='"+info+"'where usrID='"+usrID+"'";
           cnn.Open();
           int i = (int)cmd.ExecuteNonQuery();
           cnn.Close();
           if (1 == i)
           {
               result = "OK";
           }
           else
           {

               result = "ER";
           }
           return result;
       }
       public static string UpdateInfoDid(string did, string name, string info)
       {
           string result = "";
           SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
           SqlCommand cmd = cnn.CreateCommand();
           cmd.CommandText = "update Tail set " + name + "='" + info + "'where deviceID='" + did + "'";
           cnn.Open();
           int i = (int)cmd.ExecuteNonQuery();
           cnn.Close();
           if (1 == i)
           {
               result = "OK";
           }
           else
           {

               result = "ER";
           }
           return result;
       }
       public static string DeleteInof(string usrid)
       {
           string result = "";
           SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
           SqlCommand cmd = cnn.CreateCommand();
           cmd.CommandText = "delete from Tail where usrID='"+usrid+"'";
           cnn.Open();
           int i = (int)cmd.ExecuteNonQuery();
           cnn.Close();
           if (1 == i)
           {
               result = "OK";
           }
           else
           {

               result = "ER";
           }
           return result;
       }
       public static string getMessage(string uid)
       {
           string result = "ER";
           SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
           SqlCommand cmd = cnn.CreateCommand();
           cmd.CommandText = "select message from Tail where uid='" + uid + "'";
           cnn.Open();
           result = Convert.ToString(cmd.ExecuteScalar());
           cnn.Close();

           return result;
       
       }
       public static string getInfo(string uid, string name)
       {
           string result = "ER";
           SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
           SqlCommand cmd = cnn.CreateCommand();
           cmd.CommandText ="select "+name +" from Tail where usrID='" + uid + "'";
           cnn.Open();
           result = Convert.ToString(cmd.ExecuteScalar());
           cnn.Close();
           return result;
       }
       public static string appendLast(string uid, string name, string info) {
           string a = getInfo(uid, name);
           return UpdateInfo(uid, name, a + info);
       }
       public static string getKey(string uid)
       {
           string result = "ER";
           SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
           SqlCommand cmd = cnn.CreateCommand();
           cmd.CommandText = "select code from Tail where uid='" + uid + "'";
           cnn.Open();
           result = Convert.ToString(cmd.ExecuteScalar());
           cnn.Close();

           return result;
       }
       public static bool isExistdid(string did)
       {
           bool result = false;
           SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
           SqlCommand cmd = cnn.CreateCommand();
           cmd.CommandText = "select count(*) from Tail where deviceID='" + did + "'";
           cnn.Open();
           int i = Convert.ToInt32(cmd.ExecuteScalar());
           cnn.Close();
           return result;
       
       }
      
    }
}